RootDefinition

V8 Message Definiton

RootDefinition records are sourced from the listing exchange for future options and from OCC for US equity options. Records are updated as SpiderRock receives changes.

METADATA

Attribute	Value
Topic	4335-product-definition
MLink Token	OptionDefinition
Product	SRAnalytics
accessType	SELECT

Table Definition

Field	Type	Key	Default Value	Comment
root_at	enum - AssetType	PRI	'None'
root_ts	enum - TickerSrc	PRI	'None'
root_tk	VARCHAR(12)	PRI	''
ticker_at	enum - AssetType		'None'	master underlying can be a stockproduct group eg ES
ticker_ts	enum - TickerSrc		'None'	master underlying can be a stockproduct group eg ES
ticker_tk	VARCHAR(12)	SEC	''	master underlying can be a stockproduct group eg ES
osiRoot	VARCHAR(8)	SEC	''	long version of the root the short version is used in the TickerKey for example RYAAY1 not RYAA1
ccode_at	enum - AssetType		'None'
ccode_ts	enum - TickerSrc		'None'
ccode_tk	VARCHAR(12)	SEC	''
uPrcDriverKey_at	enum - AssetType		'None'	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_ts	enum - TickerSrc		'None'	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_tk	VARCHAR(12)		''	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_yr	SMALLINT UNSIGNED		0	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_mn	TINYINT UNSIGNED		0	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey_dy	TINYINT UNSIGNED		0	optional option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverType	enum - SpdrKeyType		'None'	Stock or Future note if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used
uPrcDriverKey2_at	enum - AssetType		'None'	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_ts	enum - TickerSrc		'None'	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_tk	VARCHAR(12)		''	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_yr	SMALLINT UNSIGNED		0	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_mn	TINYINT UNSIGNED		0	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverKey2_dy	TINYINT UNSIGNED		0	optional alternate option underlier price driver all option expiries overrides optExpiryDefinition
uPrcDriverType2	enum - SpdrKeyType		'None'	Stock or Future note if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used
uPrcBoundCCode	enum - YesNo		'None'	if Yes and if a future exists with ccodeCCode and futExpiry optExpiry the use this future as a pricing bound
expirationMap	enum - ExpirationMap		'None'	determines the underlying future if any
underlierMode	enum - UnderlierMode		'None'
optionType	enum - OptionType		'None'	indicator for option type
multihedge	enum - Multihedge		'None'	indicates type of multihedge None standard root all other root types are not None
exerciseTime	enum - ExerciseTime		'None'	Exercise time type
exerciseType	enum - ExerciseType		'None'	Exercise style
timeMetric	enum - TimeMetric		'None'	trading time metric 252 or 365 trading days or a weekly cycle type
tradingPeriod	enum - TradingPeriod		'None'
pricingModel	enum - PricingModel		'None'
moneynessType	enum - MoneynessType		'None'	moneyness xAxis convention PctStd K fUPrc 1 axisVol RT LogStd LOGKfUPrc axisVol RT NormStd K fUPrc axisVol RT
priceQuoteType	enum - PriceQuoteType		'None'	quoting style for the option series on the exchange price standard price quote or volatility quoted vol points
volumeTier	enum - VolumeTier		'None'
positionLimit	INT		0	max contract limit
exchanges	VARCHAR(24)		''	exchange codes
tickValue	FLOAT		0	NLV value of a single tick change in display premium pointValue tickValue tickSize
pointValue	FLOAT		0	NLV value of a single point change in display premium pointValue tickValue tickSize
pointCurrency	enum - Currency		'None'
strikeScale	DOUBLE		0	manual strike price adjustment multiplier used for some CME products if set otherwise displayFactor is used okeyxx strikePrice manualStrikeScale
strikeRatio	FLOAT		0	note effective strike strike strikeRatio cashOnExercise
cashOnExercise	FLOAT		0	note cashOnExercise is positive if it decreases the effective strike price
underliersPerCn	INT		0	note always 100 if underlying list is in use
premiumMult	DOUBLE		0	note OCC premiumstrike multiplier usually 100
symbolRatio	FLOAT		0	note currently used when AdjConvention is None value of 0 implies symbolRatio is 1
adjConvention	enum - AdjConvention		'None'
optPriceInc	enum - OptPriceInc		'None'
priceFormat	enum - PriceFormat		'None'	price display format
tradeCurr	enum - Currency		'None'
settleCurr	enum - Currency		'None'
strikeCurr	enum - Currency		'None'
defaultSurfaceRoot_at	enum - AssetType		'None'	fallback ticker to use for option surfaces if no native surfaces are available
defaultSurfaceRoot_ts	enum - TickerSrc		'None'	fallback ticker to use for option surfaces if no native surfaces are available
defaultSurfaceRoot_tk	VARCHAR(12)		''	fallback ticker to use for option surfaces if no native surfaces are available
ricRoot	VARCHAR(6)		''	RIC Root
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'
ExchangeList	JSON		'JSON_OBJECT()'
UnderlyingList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
root_tk	1
root_at	2
root_ts	3

SECONDARY INDEX (CCodeIndex) (Not Unique)

Field	Sequence
ccode_tk	1

SECONDARY INDEX (OSIRootIndex) (Not Unique)

Field	Sequence
osiRoot	1

SECONDARY INDEX (TickerIndex) (Not Unique)

Field	Sequence
ticker_tk	1

JSON Block (ExchangeList)

Field	Type	Comment
optExch	enum - optExch
root	TickerKey

JSON Block (UnderlyingList)

Field	Type	Comment
ticker	TickerKey
spc	float	note root basket sumspc ticker 100

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRAnalytics`.`MsgRootDefinition` (
    `root_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `root_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `root_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'master underlying (can be a stock/product group; eg. @ES)',
    `osiRoot` VARCHAR(8) NOT NULL DEFAULT '' COMMENT 'long version of the root.  the short version is used in the TickerKey (for example RYAAY1, not RYAA1)',
    `ccode_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ccode_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ccode_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `uPrcDriverKey_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverType` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
    `uPrcDriverKey2_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey2_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey2_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey2_yr` SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey2_mn` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverKey2_dy` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '(optional) alternate option underlier price driver (all option expiries) (overrides optExpiryDefinition)',
    `uPrcDriverType2` ENUM('None','Stock','Future','Option','MLeg') NOT NULL DEFAULT 'None' COMMENT 'Stock or Future (note: if Future and uPrcDriverKey does not have an expiry month then FrontMonth will be used)',
    `uPrcBoundCCode` ENUM('None','Yes','No') NOT NULL DEFAULT 'None' COMMENT 'if Yes and if a future exists with ccode=CCode and futExpiry = optExpiry the use this future as a pricing bound',
    `expirationMap` ENUM('None','ExactMatch','UnderlierMap') NOT NULL DEFAULT 'None' COMMENT 'determines the underlying future (if any)',
    `underlierMode` ENUM('None','Actual','FrontMonth','UPrcAdj') NOT NULL DEFAULT 'None',
    `optionType` ENUM('None','Equity','Index','Future','Binary','Warrant','Flex','MapError') NOT NULL DEFAULT 'None' COMMENT 'indicator for option type',
    `multihedge` ENUM('None','Simple','Complex','AllCash','Binary','Fragment') NOT NULL DEFAULT 'None' COMMENT 'indicates type of multihedge (None = standard root; all other root types are not None)',
    `exerciseTime` ENUM('None','PM','AM') NOT NULL DEFAULT 'None' COMMENT 'Exercise time type',
    `exerciseType` ENUM('None','American','European','Asian','Cliquet') NOT NULL DEFAULT 'None' COMMENT 'Exercise style',
    `timeMetric` ENUM('None','D252','D365','SRV6') NOT NULL DEFAULT 'None' COMMENT 'trading time metric - 252 or 365 trading days or a weekly cycle type',
    `tradingPeriod` ENUM('None','NMS','NMS_EXT','NMS_GTH','CME_ES','CME_ZC','CME_ZN','CME_CL','CME_GC','CFE','ICE_US','ICE_EU','EUX','EU_ERX','EU_CBOE','EU_NXAM','EU_NXBR','EU_NXLS','EU_NXML','EU_NXOS','EU_NXP') NOT NULL DEFAULT 'None',
    `pricingModel` ENUM('None','Equity','FutureApprox','FutureExact','NormalApprox','NormalExact') NOT NULL DEFAULT 'None',
    `moneynessType` ENUM('None','PctStd','LogStd','NormStd') NOT NULL DEFAULT 'None' COMMENT 'moneyness (xAxis) convention: PctStd = (K / fUPrc - 1) / (axisVol * RT), LogStd = LOG(K/fUPrc) / (axisVol * RT), NormStd = (K - fUPrc) / (axisVol * RT)',
    `priceQuoteType` ENUM('None','Price','Vol') NOT NULL DEFAULT 'None' COMMENT 'quoting style for the option series on the exchange, price (standard price quote) or volatility quoted (vol points)',
    `volumeTier` ENUM('None','Top50') NOT NULL DEFAULT 'None',
    `positionLimit` INT NOT NULL DEFAULT 0 COMMENT 'max contract limit',
    `exchanges` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'exchange codes',
    `tickValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single tick change in display premium	(pointValue = tickValue / tickSize)',
    `pointValue` FLOAT NOT NULL DEFAULT 0 COMMENT '$NLV value of a single point change in display premium (pointValue = tickValue / tickSize)',
    `pointCurrency` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `strikeScale` DOUBLE NOT NULL DEFAULT 0 COMMENT 'manual strike price adjustment multiplier (used for some CME products if set, otherwise displayFactor is used) (okey_xx = strikePrice * manualStrikeScale)',
    `strikeRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: effective strike = strike * strikeRatio - cashOnExercise',
    `cashOnExercise` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: cashOnExercise is positive if it decreases the effective strike price',
    `underliersPerCn` INT NOT NULL DEFAULT 0 COMMENT 'note: always 100 if underlying list is in use',
    `premiumMult` DOUBLE NOT NULL DEFAULT 0 COMMENT 'note: OCC premium/strike multiplier (usually 100)',
    `symbolRatio` FLOAT NOT NULL DEFAULT 0 COMMENT 'note: currently used when AdjConvention is None, value of 0 implies symbolRatio is 1',
    `adjConvention` ENUM('None','Original','OSI','SpcOnly','OSIAlt') NOT NULL DEFAULT 'None',
    `optPriceInc` ENUM('None','PartPenny','PartNickle','FullPenny') NOT NULL DEFAULT 'None',
    `priceFormat` ENUM('None','N0','N1','N2','N3','N4','N5','N6','N7','F4','F8','Q8','F16','F32','H32','Q32','F64','H64','FullPenny','PartPenny','PartNickle','EQT','V1','V2','V3','V4','V5','V6','V7','V8','V9','V10','V11','V12','V13','V14','V15','A0','A1','A2','A3','A4','A5','A6','A7','E32') NOT NULL DEFAULT 'None' COMMENT 'price display format',
    `tradeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `settleCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `strikeCurr` ENUM('None','AUD','BRL','CAD','CHF','CNH','CNY','EUR','GBP','JPY','KRW','MXN','MYR','NOK','NZD','SEK','TRY','USD','USDCents','CZK','ZAR','HUF','USX','GBX') NOT NULL DEFAULT 'None',
    `defaultSurfaceRoot_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
    `defaultSurfaceRoot_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
    `defaultSurfaceRoot_tk` VARCHAR(12) NOT NULL DEFAULT '' COMMENT 'fallback ticker to use for option surfaces if no native surfaces are available',
    `ricRoot` VARCHAR(6) NOT NULL DEFAULT '' COMMENT 'RIC Root',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000',
    `ExchangeList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(ExchangeList)),
    `UnderlyingList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(UnderlyingList)),
    PRIMARY KEY USING HASH (`root_tk`,`root_at`,`root_ts`),
    KEY `CCodeIndex` (`ccode_tk`) USING HASH,
    KEY `OSIRootIndex` (`osiRoot`) USING HASH,
    KEY `TickerIndex` (`ticker_tk`) USING HASH
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='RootDefinition records are sourced from the listing exchange for future options and from OCC for US equity options.  Records are updated as SpiderRock receives changes.';

SELECT TABLE EXAMPLE QUERY

SELECT
    `root_at`,
    `root_ts`,
    `root_tk`,
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `osiRoot`,
    `ccode_at`,
    `ccode_ts`,
    `ccode_tk`,
    `uPrcDriverKey_at`,
    `uPrcDriverKey_ts`,
    `uPrcDriverKey_tk`,
    `uPrcDriverKey_yr`,
    `uPrcDriverKey_mn`,
    `uPrcDriverKey_dy`,
    `uPrcDriverType`,
    `uPrcDriverKey2_at`,
    `uPrcDriverKey2_ts`,
    `uPrcDriverKey2_tk`,
    `uPrcDriverKey2_yr`,
    `uPrcDriverKey2_mn`,
    `uPrcDriverKey2_dy`,
    `uPrcDriverType2`,
    `uPrcBoundCCode`,
    `expirationMap`,
    `underlierMode`,
    `optionType`,
    `multihedge`,
    `exerciseTime`,
    `exerciseType`,
    `timeMetric`,
    `tradingPeriod`,
    `pricingModel`,
    `moneynessType`,
    `priceQuoteType`,
    `volumeTier`,
    `positionLimit`,
    `exchanges`,
    `tickValue`,
    `pointValue`,
    `pointCurrency`,
    `strikeScale`,
    `strikeRatio`,
    `cashOnExercise`,
    `underliersPerCn`,
    `premiumMult`,
    `symbolRatio`,
    `adjConvention`,
    `optPriceInc`,
    `priceFormat`,
    `tradeCurr`,
    `settleCurr`,
    `strikeCurr`,
    `defaultSurfaceRoot_at`,
    `defaultSurfaceRoot_ts`,
    `defaultSurfaceRoot_tk`,
    `ricRoot`,
    `timestamp`,
    `ExchangeList`,
    `UnderlyingList`
FROM `SRAnalytics`.`MsgRootDefinition`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `root_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `root_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `root_tk` = 'Example_root_tk';

Doc Columns Query

SELECT * FROM SRAnalytics.doccolumns WHERE TABLE_NAME='RootDefinition' ORDER BY ordinal_position ASC;